
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/02/2012 22:09:42
-- Generated from EDMX file: E:\Проект\ЧГК\ChGKWebSuite\trunk\ChGKWebSuite\ChGK.Data\CHGKModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [u351682];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_TeamAnswer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Answers] DROP CONSTRAINT [FK_TeamAnswer];
GO
IF OBJECT_ID(N'[dbo].[FK_TourAnswer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Answers] DROP CONSTRAINT [FK_TourAnswer];
GO
IF OBJECT_ID(N'[dbo].[FK_CompetitionTour]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Tours] DROP CONSTRAINT [FK_CompetitionTour];
GO
IF OBJECT_ID(N'[dbo].[FK_UserTeam]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Teams] DROP CONSTRAINT [FK_UserTeam];
GO
IF OBJECT_ID(N'[dbo].[FK_CompetitionUser]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_CompetitionUser];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Answers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Answers];
GO
IF OBJECT_ID(N'[dbo].[Competitions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Competitions];
GO
IF OBJECT_ID(N'[dbo].[Teams]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Teams];
GO
IF OBJECT_ID(N'[dbo].[Tours]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Tours];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Answers'
CREATE TABLE [dbo].[Answers] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Value] nvarchar(255)  NOT NULL,
    [QuestionNumber] smallint  NOT NULL,
    [IsCorrect] bit  NULL,
    [CollectedByWeb] bit  NOT NULL,
    [TeamID] int  NOT NULL,
    [TourID] int  NOT NULL,
    [TimeStamp] datetime  NOT NULL,
    [isLate] bit  NULL
);
GO

-- Creating table 'Competitions'
CREATE TABLE [dbo].[Competitions] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Type] nvarchar(30)  NOT NULL,
    [Description] nvarchar(1000)  NOT NULL,
    [IsEnded] bit  NOT NULL
);
GO

-- Creating table 'Teams'
CREATE TABLE [dbo].[Teams] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Number] smallint  NOT NULL,
    [Caption] nvarchar(50)  NOT NULL,
    [Disqualified] bit  NOT NULL,
    [UserLogin] nvarchar(50)  NOT NULL,
    [CompetitionID] int  NOT NULL,
    [City] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Tours'
CREATE TABLE [dbo].[Tours] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [NumberOfQuestions] smallint  NOT NULL,
    [Name] nvarchar(50)  NULL,
    [CompetitionID] int  NOT NULL,
    [NumberInCompetition] int  NOT NULL,
    [NumberOfNullQuestions] int  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Login] nvarchar(50)  NOT NULL,
    [HashCode] nvarchar(50)  NOT NULL,
    [TeamID] int  NULL,
    [Role] nvarchar(25)  NOT NULL,
    [CompetitionID] int  NOT NULL,
    [IsLoggedIn] bit  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [PK_Answers]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Competitions'
ALTER TABLE [dbo].[Competitions]
ADD CONSTRAINT [PK_Competitions]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Teams'
ALTER TABLE [dbo].[Teams]
ADD CONSTRAINT [PK_Teams]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Tours'
ALTER TABLE [dbo].[Tours]
ADD CONSTRAINT [PK_Tours]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [Login] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Login] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [TeamID] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [FK_TeamAnswer]
    FOREIGN KEY ([TeamID])
    REFERENCES [dbo].[Teams]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TeamAnswer'
CREATE INDEX [IX_FK_TeamAnswer]
ON [dbo].[Answers]
    ([TeamID]);
GO

-- Creating foreign key on [TourID] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [FK_TourAnswer]
    FOREIGN KEY ([TourID])
    REFERENCES [dbo].[Tours]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_TourAnswer'
CREATE INDEX [IX_FK_TourAnswer]
ON [dbo].[Answers]
    ([TourID]);
GO

-- Creating foreign key on [CompetitionID] in table 'Tours'
ALTER TABLE [dbo].[Tours]
ADD CONSTRAINT [FK_CompetitionTour]
    FOREIGN KEY ([CompetitionID])
    REFERENCES [dbo].[Competitions]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompetitionTour'
CREATE INDEX [IX_FK_CompetitionTour]
ON [dbo].[Tours]
    ([CompetitionID]);
GO

-- Creating foreign key on [UserLogin] in table 'Teams'
ALTER TABLE [dbo].[Teams]
ADD CONSTRAINT [FK_UserTeam]
    FOREIGN KEY ([UserLogin])
    REFERENCES [dbo].[Users]
        ([Login])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserTeam'
CREATE INDEX [IX_FK_UserTeam]
ON [dbo].[Teams]
    ([UserLogin]);
GO

-- Creating foreign key on [CompetitionID] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_CompetitionUser]
    FOREIGN KEY ([CompetitionID])
    REFERENCES [dbo].[Competitions]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompetitionUser'
CREATE INDEX [IX_FK_CompetitionUser]
ON [dbo].[Users]
    ([CompetitionID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------